package com.xuef.repository;

import com.xuef.entity.Blog;
import com.xuef.entity.Catalog;
import com.xuef.entity.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.List;
import java.util.Set;

/**
 * Created by moveb on 2018/5/9.
 */
@Repository
public interface BlogRepository extends JpaRepository<Blog, Integer> {
    /**
     * 由 findByUserAndTitleLikeOrTagsLikeOrderByCreateTimeDesc 代替，可以根据标签进行查询
     * @param user
     * @param title
     * @param pageable
     * @return
     */
    @Deprecated
    Page<Blog> findByUserAndTitleLikeOrderByCreateTimeDesc(User user, String title, Pageable pageable);

    /**
     * @param user
     * @param title
     * @param pageable
     * @return
     */
    Page<Blog> findByUserAndTitleLike(User user, String title, Pageable pageable);

    /**
     *  @param s
     * @param user1
     * @param title
     * @param user
     * @param pageable    @return
     * */
    Page<Blog> findByTitleLikeAndUserOrTagsLikeAndUserOrderByCreateTimeDesc(String s, User user1, String title,
                                                                            User user,
                                                                            Pageable pageable);
    /**
     */
    Page<Blog> findByCatalog(Catalog catalog, Pageable pageable);

    Page<Blog> findByIdIn(List<Integer> blogs, Pageable pageable);
    /**
     * 将博客文章按年月归档
     * @return
     */
    /**
    @Query(nativeQuery = true, value = "SELECT " +
            "   new com.xuef.vo.GroupBlogVO(DATE_FORMAT(b.createTime, '%Y-%m'), count(*)) " +
            "FROM " +
            "   Blog b " +
            "GROUP BY DATE_FORMAT(b.createTime, '%Y-%m')")
    List<GroupBlogVO> getBlogCount(int userId);
    */
    @Query(nativeQuery = true, value = "SELECT " +
            "  DATE_FORMAT(b.create_time, '%Y-%m') as submit_date, count(*) as bcount " +
            "FROM " +
            "   blog b " +
            "WHERE user_id=?1 " +
            "GROUP BY DATE_FORMAT(b.create_time, '%Y-%m')")
    List<Object[]> getBlogCount(int userId);

    /**
     * 更新评论数量
     * @param blogId
     */
    @Transactional
    @Modifying
        @Query(value = "UPDATE " +
            " blog b " +
            " SET b.comment_count = (select count(c.id) from comment c where c.entity_type=1 and c.entity_id=?1) " +
            " WHERE b.id=?1", nativeQuery = true)
    void updateCommentCount(int blogId);

    /**
    @Query(" SELECT b " +
            "FROM " +
            "   Blog b " +
            "WHERE b.user=:user " +
            "AND b.createTime like %:createTime%")
    */
    Page<Blog> findByUserAndCreateTimeGreaterThanEqualAndCreateTimeLessThanEqual(User user, Date start, Date end, Pageable pageable);
}
